package com.easyhome.report.tool;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.easyhome.report.common.utils.excel.ExcelImport;

public class Han_list {

	static String excelTableSheetName = "R-4 应收账款";
	
	
	static String templateFilesDir="C:\\Users\\chunqiang.yang\\Desktop\\tempotary\\excel_template\\";
	private static String sheet_name=excelTableSheetName;
	private static String tableNameStr = "";
	private static String tableNum="053";

	public static void main(String[] args) {
		/*
		 * get workbook
		 */
		String excelTempltFileName = "C:\\fileupload\\source\\"+excelTableSheetName+".xlsx";
		Workbook wb = getWorkbook(excelTempltFileName);

		tableNameStr = getTableNameFromWb(wb);
		
		/*
		 * entry
		 
		System.out.println("entry:----------------------------------------------------------------");
		writeListEntry(wb);
		
		
		 * dao
		 
		System.out.println("dao:----------------------------------------------------------------");
		writeListDao(wb);
		
		 * service impl
		 
		System.out.println("service:----------------------------------------------------------------");
		writeListService(wb);
		
		 * mapper xml
		 
		System.out.println("mapper xml:----------------------------------------------------------------");
		writeListMapperXml(wb);*/
		String table_name="FIN_088_BOND_PAYABLE";
		System.out.println("entry:----------------------------------------------------------------");
		writeListEntry1(table_name);
		
		
		
	}
	private static void writeListMapperXml(Workbook wb) {
		String hanTemplateForList_mapper = readFileByLines(templateFilesDir+"list_mapperNew.txt");
		
		//{dao_class_name}
		String excelCode = geExcelCode(wb);
		hanTemplateForList_mapper=hanTemplateForList_mapper.replace("{dao_class_name}", captanUpperCase(excelCode));
		
		hanTemplateForList_mapper=hanTemplateForList_mapper.replace("{tableNum}", captanUpperCase(tableNum));
		
		//{delete_method_name}
		hanTemplateForList_mapper=hanTemplateForList_mapper.replace("{delete_method_name}", 	"delete"+tableNameStr + "Entry");

		//delete table name 
		Sheet sheet = wb.getSheet(sheet_name);
		Row tablenameRow = sheet.getRow(0);
		Cell tableNameCell = tablenameRow.getCell(0);
		String tableNameStrOrigin = tableNameCell.getStringCellValue();
		hanTemplateForList_mapper=hanTemplateForList_mapper.replace("{table_name}", tableNameStrOrigin);
		
		
		//{save_method_name}
		hanTemplateForList_mapper=hanTemplateForList_mapper.replace("{save_method_name}", 	"save"+tableNameStr + "Entry");
		//{entry_class_name}
		String entry_class_name = tableNameStr + "Entry";
		hanTemplateForList_mapper=hanTemplateForList_mapper.replace("{entry_class_name}", entry_class_name);
		
		/*
		 * write {save_column_content}
		 */
		String save_column_content = "";
		String save_entry_content="";
		
		int rowSum = sheet.getPhysicalNumberOfRows();
		for (int i = 0; i < rowSum; i++) {
			Row row = sheet.getRow(i);
			if (row != null) {
				int cellSum = row.getPhysicalNumberOfCells();
				for (int j = 0; j < cellSum; j++) {

					Cell cell = row.getCell(j);

					String cellStr = cell == null ? "" : getStringValueFromCell(cell);
		
					if (cellStr != null && !cellStr.equals("")) {
						/*
						 * 解析cell 是否是定义的列名
						 */
						if (cellStr.startsWith("888")) {
							cellStr = cellStr.replace("888", "");
							/*
							 * write columnName str To {save_column_content}
							 */
							save_column_content+="\t,`"+cellStr+"`\n";
							/*
							 * write columnName str To {save_entry_content}
							 */
							String columnName = getColumnNameFromWb(cellStr);
							save_entry_content+=",#{"+"fin"+tableNum+"Info."+columnName+"}\n";
							
						}
					}
				}
			}

		}
		

		hanTemplateForList_mapper=hanTemplateForList_mapper.replace("{save_column_content}", save_column_content);
		hanTemplateForList_mapper=hanTemplateForList_mapper.replace("{save_entry_content}", save_entry_content);
		System.out.println(hanTemplateForList_mapper);
		
	}
	private static void writeListService(Workbook wb) {
		String hanTemplateForList_service = readFileByLines(templateFilesDir+"list_serviceImple.txt");
		
		//{dao_class_name}
		String excelCode = geExcelCode(wb);
		hanTemplateForList_service=hanTemplateForList_service.replace("{dao_class_name}", captanUpperCase(excelCode));
		
		//{entry_class_name}
		String entry_class_name = tableNameStr + "Entry";
		hanTemplateForList_service=hanTemplateForList_service.replace("{entry_class_name}", entry_class_name);
		
		//{service_code}
		hanTemplateForList_service=hanTemplateForList_service.replace("{service_code}", excelCode);
		
		//{service_name}
		hanTemplateForList_service=hanTemplateForList_service.replace("{service_name}",captanUpperCase(excelCode));
		
		//{dao_filed_name}
		hanTemplateForList_service=hanTemplateForList_service.replace("{dao_filed_name}",captanLowerCase(excelCode));
		
		//{entry_filed_name}
		hanTemplateForList_service=hanTemplateForList_service.replace("{entry_filed_name}",captanLowerCase(entry_class_name));
		
		//{service_field_content}
		/*
		 * write service_field_content
		 */
		Map<String,String> columnAndCommentMap = Han_db.getColumnAndComments(getTableNameOriginFromwb(wb));
		Map<String,String> columnAdnDataType = Han_db.getColumnAndType(getTableNameOriginFromwb(wb));
		String fieldContent = "\tRow row = sheet.getRow(i);\n";
		
		Sheet sheet = wb.getSheet(sheet_name);

		int rowSum = sheet.getPhysicalNumberOfRows();
		for (int i = 0; i < rowSum; i++) {
			Row row = sheet.getRow(i);
			if (row != null) {
				int cellSum = row.getPhysicalNumberOfCells();
				for (int j = 0; j < cellSum; j++) {

					Cell cell = row.getCell(j);

					String cellStr = cell == null ? "" : getStringValueFromCell(cell);

					if (cellStr != null && !cellStr.equals("")) {
						/*
						 * 解析cell 是否是定义的列名
						 */
						if (cellStr.startsWith("888")) {
							cellStr = cellStr.replace("888", "");
							String columnName = getColumnNameFromWb(cellStr);
							/*
							 * write columnName str To {field content}
							 */
							fieldContent+="\t\t\t\t\t// "+cellStr+"    "+columnAndCommentMap.get(cellStr)+"\n";
							String dataType = columnAdnDataType.get(cellStr);
							if("varchar".equalsIgnoreCase(dataType)){
								
								//fieldContent+="\t\t\t\t\tCell "+columnName+"Cell = row.getCell("+cell.getColumnIndex()+");\n";
								
								String entryFieldName =  "\t\t\t\t\t"+captanLowerCase(entry_class_name);
								fieldContent+=entryFieldName+".set"+captanUpperCase(columnName)+"((row.getCell("+cell.getColumnIndex()+")"+".getStringCellValue()));\n";
								
								///Fin085LtBorrowDetailEntry.setComName((row.getCell(1).getStringCellValue()));
							}else if("datetime".equalsIgnoreCase(dataType)){
								
								/*fieldContent+="\t\t\t\t\tCell "+columnName+"Cell = row.getCell("+cell.getColumnIndex()+");\n";
								
								String entryFieldName = "\t\t\t\t\t"+captanLowerCase(entry_class_name);
								
								fieldContent+=entryFieldName+".set"+captanUpperCase(columnName)+"("+columnName+"Cell.getDateCellValue());\n";*/
								
								String entryFieldName = "\t\t\t\t\t"+captanLowerCase(entry_class_name);
								fieldContent+=entryFieldName+".set"+captanUpperCase(columnName)+"((row.getCell("+cell.getColumnIndex()+")"+".getDateCellValue()));\n";
							
							}else if("int".equalsIgnoreCase(dataType)){
								
								/*fieldContent+="\t\t\t\t\tCell "+columnName+"Cell = row.getCell("+cell.getColumnIndex()+");\n";
								
								String entryFieldName =  "\t\t\t\t\t"+captanLowerCase(entry_class_name);
								fieldContent+=entryFieldName+".set"+captanUpperCase(columnName)+"((int) "+columnName+"Cell.getNumericCellValue());\n";*/
							
								String entryFieldName =  "\t\t\t\t\t"+captanLowerCase(entry_class_name);
								fieldContent+=entryFieldName+".set"+captanUpperCase(columnName)+"(((int)row.getCell("+cell.getColumnIndex()+")"+".getNumericCellValue()));\n";
								
							}else if("decimal".equalsIgnoreCase(dataType)){
								/*fieldContent+="\t\t\t\t\tCell "+columnName+"Cell = row.getCell("+cell.getColumnIndex()+");\n";
								
								String entryFieldName =  "\t\t\t\t\t"+captanLowerCase(entry_class_name);
								fieldContent+=entryFieldName+".set"+captanUpperCase(columnName)+"("+columnName+"Cell.getNumericCellValue());\n";*/
								
								String entryFieldName =  "\t\t\t\t\t"+captanLowerCase(entry_class_name);
								fieldContent+=entryFieldName+".set"+captanUpperCase(columnName)+"((row.getCell("+cell.getColumnIndex()+")"+".getNumericCellValue()));\n";
							
							}else if("char".equalsIgnoreCase(dataType)){

								/*fieldContent+="\t\t\t\t\tCell "+columnName+"Cell = row.getCell("+cell.getColumnIndex()+");\n";
								
								String entryFieldName =  "\t\t\t\t\t"+captanLowerCase(entry_class_name);
								fieldContent+=entryFieldName+".set"+captanUpperCase(columnName)+"("+columnName+"Cell.getNumericCellValue());\n";*/
								
								String entryFieldName =  "\t\t\t\t\t"+captanLowerCase(entry_class_name);
								fieldContent+=entryFieldName+".set"+captanUpperCase(columnName)+"((row.getCell("+cell.getColumnIndex()+")"+".getNumericCellValue()));\n";
							}
							
							
						}
						
					}

				}
			}

		}
		fieldContent+="\t\t\t\t\t"+captanLowerCase(entry_class_name)+"List.add("+captanLowerCase(entry_class_name)+");\n";
		
		//delete 
		hanTemplateForList_service=hanTemplateForList_service.replace("{table_name_original}",getTableNameOriginFromwb(wb) );
		//save
		hanTemplateForList_service=hanTemplateForList_service.replace("{save_method}", captanUpperCase(entry_class_name));
		
		hanTemplateForList_service=hanTemplateForList_service.replace("{service_field_content}", fieldContent);
		
		System.out.println(hanTemplateForList_service);
	}
	private static void writeListDao(Workbook wb) {
		String hanTemplateForList_dao = readFileByLines(templateFilesDir+"list_dao.txt");
		/*
		 * write entry class import
		 */
		String entry_class_name = tableNameStr + "Entry";
		hanTemplateForList_dao = hanTemplateForList_dao.replace("{entry_class_name}", entry_class_name);
		
		String excelCode = geExcelCode(wb);
		
		String daoClassName = captanUpperCase(excelCode)+"ReportDao";
		hanTemplateForList_dao=hanTemplateForList_dao.replace("{dao_class_name}", captanUpperCase(excelCode)+"ReportDao");
		
		hanTemplateForList_dao=hanTemplateForList_dao.replace("{delete_method}", captanUpperCase(entry_class_name));
		hanTemplateForList_dao=hanTemplateForList_dao.replace("{save_method}", captanUpperCase(entry_class_name));
		hanTemplateForList_dao=hanTemplateForList_dao.replace("{entry_class_name_parameter}", captanLowerCase(entry_class_name));
		
		System.out.println(hanTemplateForList_dao);


	}
	
	private static String geExcelCode(Workbook wb){
		Sheet sheet =wb.getSheet(sheet_name);
		Row row = sheet.getRow(0);
		Cell cell = row.getCell(1);
		String excelCode = cell.getStringCellValue();
		return excelCode;
	}
	
	private static String geExcelShortCode(Workbook wb){
		Sheet sheet =wb.getSheet(sheet_name);
		Row row = sheet.getRow(0);
		Cell cell = row.getCell(1);
		String excelCode = cell.getStringCellValue();
		excelCode = excelCode.substring(0,6);
		return excelCode;
	}
	private static void writeListEntry(Workbook wb) {
		String hanTemplateForList = readFileByLines(templateFilesDir+"list_entry.txt");
		Map<String,String> columnAndCommentMap = Han_db.getColumnAndComments(getTableNameOriginFromwb(wb));
		Map<String,String> columnAdnDataType = Han_db.getColumnAndType(getTableNameOriginFromwb(wb));
		/*
		 * write entry class name
		 */
		hanTemplateForList = hanTemplateForList.replace("{entryClassName}", tableNameStr + "Entry");

		/*
		 * write entry_field_content
		 */
		String fieldContent = "";
		String entry_getterAndSetter_Content="";
		
		Sheet sheet = wb.getSheet(sheet_name);

		int rowSum = sheet.getPhysicalNumberOfRows();
		for (int i = 0; i < rowSum; i++) {
			Row row = sheet.getRow(i);
			if (row != null) {
				int cellSum = row.getPhysicalNumberOfCells();
				for (int j = 0; j < cellSum; j++) {

					Cell cell = row.getCell(j);

					String cellStr = cell == null ? "" : getStringValueFromCell(cell);

					if (cellStr != null && !cellStr.equals("")) {
						/*
						 * 解析cell 是否是定义的列名
						 */
						if (cellStr.startsWith("888")) {
							cellStr = cellStr.replace("888", "");
							String columnName = getColumnNameFromWb(cellStr);
							/*
							 * write columnName str To {field content}
							 */
							fieldContent+="\t// "+cellStr+"    "+columnAndCommentMap.get(cellStr)+"\n";
							String dataType = columnAdnDataType.get(cellStr);
							String dataTypeForJavaFile = "";
							if("varchar".equalsIgnoreCase(dataType)){
								dataTypeForJavaFile = "String";
							}else if("datetime".equalsIgnoreCase(dataType)){
								dataTypeForJavaFile = "Date";
							}else if("int".equalsIgnoreCase(dataType)){
								dataTypeForJavaFile = "int";
							}else if("decimal".equalsIgnoreCase(dataType)){
								dataTypeForJavaFile = "double";
							}else if("char".equalsIgnoreCase(dataType)){
								dataTypeForJavaFile = "double";
							}
							fieldContent+="\tprivate "+dataTypeForJavaFile+" "+columnName+";\n";
							/*
							 * write {entry_getterAndSetter_Content}
							 */
							entry_getterAndSetter_Content+="\tpublic "+dataTypeForJavaFile+" get"+columnName.substring(0, 1).toUpperCase() 
									+ columnName.substring(1)+"(){\n\t\treturn "+columnName+";\n\t}\n\n";
							
							entry_getterAndSetter_Content+="\tpublic void set"+columnName.substring(0, 1).toUpperCase() 
									+ columnName.substring(1)+"("+dataTypeForJavaFile+" "+columnName+"){\n\t\tthis."+columnName+"="+columnName+";\n\t}\n\n";
						}
					}
				}
			}

		}
		
		hanTemplateForList=hanTemplateForList.replace("{entry_field_Content}", fieldContent);
		hanTemplateForList=hanTemplateForList.replace("{entry_getterAndSetter_Content}", entry_getterAndSetter_Content);
		System.out.println(hanTemplateForList);
		

	}
	
	private static void writeListEntry1(String table_name) {
		
		Map<String,String> columnAndCommentMap = Han_db.getColumnAndComments(table_name);
		Map<String,String> columnAdnDataType = Han_db.getColumnAndType(table_name);
		//@Table("FIN_092_TAX_DEFER")
		System.out.println("@Table(\""+table_name+"\")");
		System.out.println();
		String fieldContent = "";
		
		for(Map.Entry<String,String> entry : columnAndCommentMap.entrySet()) {
			/*// ITEM    项目
			@ColumnName("ITEM")*/
			String column = entry.getKey().toString();  
			String comment = entry.getValue().toString();  
			fieldContent+="\t//"+column+"     "+ comment+"\n";
			fieldContent+="\t@ColumnName(\""+column+"\")"+"\n";
			String dataType = columnAdnDataType.get(column);
			String dataTypeForJavaFile = "";
			if("varchar".equalsIgnoreCase(dataType)){
				dataTypeForJavaFile = "String";
			}else if("datetime".equalsIgnoreCase(dataType)){
				dataTypeForJavaFile = "Date";
			}else if("int".equalsIgnoreCase(dataType)){
				dataTypeForJavaFile = "int";
			}else if("decimal".equalsIgnoreCase(dataType)){
				dataTypeForJavaFile = "double";
			}else if("char".equalsIgnoreCase(dataType)){
				dataTypeForJavaFile = "double";
			}
			String columnName = getColumnNameFromWb(column);
			fieldContent+="\tprivate "+dataTypeForJavaFile+" "+columnName+";\n";
		}
		
		System.out.println(fieldContent);
		
		int count=0;
		fieldContent = "";
		for(Map.Entry<String,String> entry : columnAndCommentMap.entrySet()) {
			String column = entry.getKey().toString();  
			String comment = entry.getValue().toString();  
			
			String dataType = columnAdnDataType.get(column);
			String columnName = getColumnNameFromWb(column);
			String[] strList = tableNameStr.split("_");
			String entry_class_name="";
			String result = "";
			for (String str : strList) {
				result += captanUpperCase(str.toLowerCase());
			}
			entry_class_name=result;
			//Fin085LtBorrowDetailEntry.setGuaComName(row.getCell(15).getStringCellValue());
			if("varchar".equalsIgnoreCase(dataType)){
				
				
				String entryFieldName =  "\t\t\t\t\t"+captanLowerCase(entry_class_name);
				//fieldContent+=entryFieldName+".set"+captanUpperCase(columnName)+"(row.getCell("+count+")"+".getStringCellValue());\n";
				fieldContent+=entryFieldName+".set"+captanUpperCase(columnName)+"(ExcelImport.getCell(row,"+count+")"+".getStringCellValue());\n";
			}else if("datetime".equalsIgnoreCase(dataType)){
				
				String entryFieldName = "\t\t\t\t\t"+captanLowerCase(entry_class_name);
				//fieldContent+=entryFieldName+".set"+captanUpperCase(columnName)+"(row.getCell("+count+")"+".getDateCellValue());\n";
				fieldContent+=entryFieldName+".set"+captanUpperCase(columnName)+"(ExcelImport.getCell(row,"+count+")"+".getDateCellValue());\n";
			}else if("int".equalsIgnoreCase(dataType)){
			
				String entryFieldName =  "\t\t\t\t\t"+captanLowerCase(entry_class_name);
				//fieldContent+=entryFieldName+".set"+captanUpperCase(columnName)+"((int)row.getCell("+count+")"+".getNumericCellValue());\n";
				fieldContent+=entryFieldName+".set"+captanUpperCase(columnName)+"((int)ExcelImport.getCell(row,"+count+")"+".getNumericCellValue());\n";
			}else if("decimal".equalsIgnoreCase(dataType)){
				
				String entryFieldName =  "\t\t\t\t\t"+captanLowerCase(entry_class_name);
				//fieldContent+=entryFieldName+".set"+captanUpperCase(columnName)+"(row.getCell("+count+")"+".getNumericCellValue());\n";
				fieldContent+=entryFieldName+".set"+captanUpperCase(columnName)+"(ExcelImport.getCell(row,"+count+")"+".getNumericCellValue());\n";
			}else if("char".equalsIgnoreCase(dataType)){
				
				String entryFieldName =  "\t\t\t\t\t"+captanLowerCase(entry_class_name);
				//fieldContent+=entryFieldName+".set"+captanUpperCase(columnName)+"(row.getCell("+count+")"+".getNumericCellValue());\n";
				fieldContent+=entryFieldName+".set"+captanUpperCase(columnName)+"(ExcelImport.getCell(row,"+count+")"+".getNumericCellValue());\n";
			}
			count++;
			
		}
		System.out.println(fieldContent);
		
		
		
		
	}


	public static String captanUpperCase(String str) {
		return str.substring(0, 1).toUpperCase() + str.substring(1);
	}
	
	public static String captanLowerCase(String str) {
		return str.substring(0, 1).toLowerCase() + str.substring(1);
	}

	private static String getStringValueFromCell(Cell cell) {
		CellType cellTypeEnum = cell.getCellTypeEnum();
		if (cellTypeEnum == cellTypeEnum.NUMERIC) {
			double d = cell.getNumericCellValue();
			return d + "";
		} else if (cellTypeEnum == cellTypeEnum.STRING) {
			return cell.getStringCellValue();
		} else if (cellTypeEnum == cellTypeEnum.BOOLEAN) {
			return cell.getBooleanCellValue() + "";
		} else if (cellTypeEnum == cellTypeEnum.FORMULA) {
			return cell.getCellFormula();
		} else {
			return "";
		}

	}

	private static String getColumnNameFromWb(String columnStr) {
		String[] strList = columnStr.split("_");

		String result = "";
		for (String str : strList) {
			result += captanUpperCase(str.toLowerCase());
		}
		
		result = result.substring(0, 1).toLowerCase() + result.substring(1);
		return result;
	}

	private static String getTableNameFromWb(Workbook wb) {
		/*
		 * get the tableName from wb
		 */
		Sheet sheet = wb.getSheet(sheet_name);
		Row tablenameRow = sheet.getRow(0);
		Cell tableNameCell = tablenameRow.getCell(0);
		String tableNameStr = tableNameCell.getStringCellValue();
		String[] strList = tableNameStr.split("_");

		String result = "";
		for (String str : strList) {
			result += captanUpperCase(str.toLowerCase());
		}

		return result;
	}
	private static String getTableNameOriginFromwb(Workbook wb) {
		/*
		 * get the tableName from wb
		 */
		Sheet sheet = wb.getSheet(sheet_name);
		Row tablenameRow = sheet.getRow(0);
		Cell tableNameCell = tablenameRow.getCell(0);
		String tableNameStr = tableNameCell.getStringCellValue();
		
		return tableNameStr;
	}

	private static Workbook getWorkbook(String excelTempltFileName) {

		InputStream is = null;
		Workbook wb = null;

		try {
			String filename = excelTempltFileName;
			is = new FileInputStream(new File(excelTempltFileName));
			if (ExcelImport.isExcel2007(filename)) {
				wb = new XSSFWorkbook(is);
			} else {
				wb = new HSSFWorkbook(is);
			}
		} catch (Exception e) {

		}

		return wb;
	}

	public static String readFileByLines(String fileName) {
		String s = "";
		File file = new File(fileName);
		BufferedReader reader = null;
		try {
			reader = new BufferedReader(new FileReader(file));
			String tempString = null;
			int line = 1;
			// 一次读入一行，直到读入null为文件结束
			while ((tempString = reader.readLine()) != null) {
				// 显示行号
				s += tempString + "\n";
				line++;
			}
			reader.close();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (reader != null) {
				try {
					reader.close();
				} catch (IOException e1) {
				}
			}
		}
		return s;
	}

	public static boolean isExcel2007(String filePath) {
		return filePath.matches("^.+\\.(?i)(xlsx)$");
	}

}
